package co.th.helper.dao;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import org.apache.log4j.Logger;

import co.th.helper.common.CommonUtils;
import co.th.helper.jdbc.JdbcTemplate;
import co.th.helper.jdbc.mapper.ProductRowMapper;
import co.th.helper.model.Product;

public class ProductDAO extends JdbcTemplate<Product>{

	private static Logger _logger = Logger.getLogger(ProductDAO.class);
	
	public List<Product> findAll() throws Exception{
		try {
			return findByCriteria(null);
		} catch (Exception ex) {
			_logger.error("Exception : " + ex.getMessage());
			throw new RuntimeException("Can't get all Product Data.");
		}
	}
	
	public Product findByPK(BigDecimal productCode, BigDecimal productYear) throws Exception{
		try {
			Product model = new Product();
			model.setProductCode(productCode);
			model.setProductYear(productYear);
			
			List<Product> list = findByCriteria(model);
			if (null == list || list.isEmpty()){
				throw new Exception("Record not found.");
			}
			return list.get(0);
		} catch (Exception ex) {
			_logger.error("Exception : " + ex.getMessage());
			throw new RuntimeException("Cat not get Product.");
		}
	}
	
	public List<Product> findByCriteria(Product model) throws Exception{
		try {
			StringBuffer sql = new StringBuffer("select * from product where 1=1 ");
			List<Object> paramObj = new ArrayList<Object>(0);
			
			if (null != model){
				int index = 0;
				if (!CommonUtils.chkIsnull(model.getProductCode())){
					sql.append(" and product_code = ? ");
					paramObj.add(index++, model.getProductCode());
				}
				if (!CommonUtils.chkIsnull(model.getProductYear())){
					sql.append(" and product_year = ? ");
					paramObj.add(index++, model.getProductYear());
				}
				if (!CommonUtils.chkIsnull(model.getProductNameTh())){
					sql.append(" and product_name_th = ? ");
					paramObj.add(index++, model.getProductNameTh());
				}
				if (!CommonUtils.chkIsnull(model.getProductNameEn())){
					sql.append(" and product_name_en = ? ");
					paramObj.add(index++, model.getProductNameEn());
				}
				if (!CommonUtils.chkIsnull(model.getProductDetail())){
					sql.append(" and product_detail = ? ");
					paramObj.add(index++, model.getProductDetail());
				}
				if (null != model.getCategory() && !CommonUtils.chkIsnull(model.getCategory().getCategoryCode())){
					sql.append(" and category = ? ");
					paramObj.add(index++, model.getCategory().getCategoryCode());
				}
				if (null != model.getBrand() && !CommonUtils.chkIsnull(model.getBrand().getBrandCode())){
					sql.append(" and brand = ? ");
					paramObj.add(index++, model.getBrand().getBrandCode());
				}
				if (!CommonUtils.chkIsnull(model.getSupplier())){
					sql.append(" and supplier = ? ");
					paramObj.add(index++, model.getSupplier());
				}
				if (!CommonUtils.chkIsnull(model.getActive())){
					sql.append(" and active = ? ");
					paramObj.add(index++, model.getActive());
				}
			}
			return this.queryList(sql.toString(), CommonUtils.convListToArray(paramObj), new ProductRowMapper());
		} catch (Exception ex) {
			_logger.error("Exception : " + ex.getMessage());
			throw new RuntimeException("Can't get Product");
		}
	}
	
	public synchronized Product insert(Product model) throws Exception{
		try {
			if (null == model){
				throw new Exception("No Product of request.");
			}else if (CommonUtils.chkIsnull(model.getProductYear())){
				throw new Exception("No primary key (Product Year).");
			}
			model.setProductCode(generatePK(model.getProductYear()));
			
			final String sql = "INSERT INTO product (product_code, product_year, product_name_th, product_name_en, product_detail, image, category, brand, supplier, active, create_by, create_date, update_by, update_date) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
			Object[] params = new Object[14];
			params[0] = model.getProductCode();
			params[1] = model.getProductYear();
			params[2] = model.getProductNameTh();
			params[3] = model.getProductNameEn();
			params[4] = model.getProductDetail();
			params[5] = model.getImage();
			params[6] = model.getCategory();
			params[7] = model.getBrand();
			params[8] = model.getSupplier();
			params[9] = model.getActive();			
			params[10] = model.getCreateBy();
			params[11] = model.getCreateDate();
			params[12] = model.getUpdateBy();
			params[13] = model.getUpdateDate();
			
			int rowOfProcess = this.saveUnique(sql, params);
			if (rowOfProcess < 1){
				throw new Exception("Can't save Product data.");
			}
			return model;
		} catch (Exception ex) {
			_logger.error("Exception : " + ex.getMessage());
			throw ex;
		}
	}
	
	public int update(Product model) throws Exception{
		try {
			if (null == model){
				throw new Exception("No Product of request.");
			}else if (CommonUtils.chkIsnull(model.getProductCode())){
				throw new Exception("No primary key (Product Code).");
			}else if (CommonUtils.chkIsnull(model.getProductYear())){
				throw new Exception("No primary key (Product Year).");
			}
			
			StringBuffer sql = new StringBuffer("UPDATE product SET ");
			List<Object> paramObj = new ArrayList<Object>(0);
			
			int index = 0;
			sql.append(" update_date = ?  ");
			paramObj.add(index++, new java.sql.Date(Calendar.getInstance().getTime().getTime()));
			
			if (!CommonUtils.chkIsnull(model.getProductNameTh())){
				sql.append(" , product_name_th = ? ");
				paramObj.add(index++, model.getProductNameTh());
			}
			if (!CommonUtils.chkIsnull(model.getProductNameEn())){
				sql.append(" , product_name_en = ? ");
				paramObj.add(index++, model.getProductNameEn());
			}
			if (!CommonUtils.chkIsnull(model.getProductDetail())){
				sql.append(" , product_detail = ? ");
				paramObj.add(index++, model.getProductDetail());
			}
			if (null != model.getImage()){
				sql.append(" , image = ? ");
				paramObj.add(index++, model.getImage());
			}
			if (null != model.getCategory() && !CommonUtils.chkIsnull(model.getCategory().getCategoryCode())){
				sql.append(" , category = ? ");
				paramObj.add(index++, model.getCategory().getCategoryCode());
			}
			if (null != model.getBrand() && !CommonUtils.chkIsnull(model.getBrand().getBrandCode())){
				sql.append(" , brand = ? ");
				paramObj.add(index++, model.getBrand().getBrandCode());
			}
			if (!CommonUtils.chkIsnull(model.getSupplier())){
				sql.append(" , supplier = ? ");
				paramObj.add(index++, model.getSupplier());
			}
			if (!CommonUtils.chkIsnull(model.getActive())){
				sql.append(" , active = ? ");
				paramObj.add(index++, model.getActive());
			}
			
			sql.append(" WHERE product_code = ? AND product_year = ? ");
			paramObj.add(index++, model.getProductCode());
			paramObj.add(index++, model.getProductYear());
			
			return updateUnique(sql.toString(), CommonUtils.convListToArray(paramObj), new ProductRowMapper());
		} catch (Exception ex) {
			_logger.error("Exception : " + ex.getMessage());
			throw new RuntimeException("Can't update Product Data.");
		}
	}
	
	public int delete(BigDecimal productCode, BigDecimal productYear) throws Exception{
		try {		
			if (CommonUtils.chkIsnull(productCode)){
				throw new Exception("No primary key (Product Code).");
			}else if (CommonUtils.chkIsnull(productYear)){
				throw new Exception("No primary key (Product Year).");
			}
			
			final String sql = "DELETE FROM product WHERE product_code = ? AND product_year = ?";
			Object[] param = new Object[]{productCode, productYear};
			
			return updateUnique(sql, param, new ProductRowMapper());
		} catch (Exception ex) {
			_logger.error("Exception : " + ex.getMessage());
			throw new RuntimeException("Can't delete Product Data.");
		}
	}
	
	private BigDecimal generatePK(BigDecimal productYear) throws Exception{
		final String sql = "SELECT MAX(product_code) FROM product WHERE product_year = ?";
		_logger.debug("SQL : " + sql);
		Product objRow = queryUnique(sql, new Object[]{productYear}, new ProductRowMapper());
		return BigDecimal.valueOf(objRow.getProductCode().intValue() + 1);
	}
}
